{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import lightgbm as lgb\n",
    "from sklearn.preprocessing import LabelEncoder\n",
    "from sklearn.model_selection import KFold\n",
    "import datetime\n",
    "import gc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "path = \"../../Resources/\"\n",
    "train_df = pd.read_csv(path + 'train.csv')\n",
    "# Remove initial outliers\n",
    "train_df = train_df.loc[train_df['building_id'] != 1099 ]\n",
    "train_df = train_df.query('not (building_id <= 104 & meter == 0 & timestamp <= \"2016-05-20\")')\n",
    "building_df = pd.read_csv(path + 'building_metadata.csv')\n",
    "weather_df = pd.read_csv(path + 'weather_train_df.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pandas.api.types import is_datetime64_any_dtype as is_datetime\n",
    "from pandas.api.types import is_categorical_dtype\n",
    "\n",
    "def reduce_mem_usage(df, use_float16=False):\n",
    "    \"\"\"\n",
    "    Iterate through all the columns of a dataframe and modify the data type to reduce memory usage.        \n",
    "    \"\"\"\n",
    "    \n",
    "    start_mem = df.memory_usage().sum() / 1024**2\n",
    "    print(\"Memory usage of dataframe is {:.2f} MB\".format(start_mem))\n",
    "    \n",
    "    for col in df.columns:\n",
    "        if is_datetime(df[col]) or is_categorical_dtype(df[col]):\n",
    "            continue\n",
    "        col_type = df[col].dtype\n",
    "        \n",
    "        if col_type != object:\n",
    "            c_min = df[col].min()\n",
    "            c_max = df[col].max()\n",
    "            if str(col_type)[:3] == \"int\":\n",
    "                if c_min > np.iinfo(np.int8).min and c_max < np.iinfo(np.int8).max:\n",
    "                    df[col] = df[col].astype(np.int8)\n",
    "                elif c_min > np.iinfo(np.int16).min and c_max < np.iinfo(np.int16).max:\n",
    "                    df[col] = df[col].astype(np.int16)\n",
    "                elif c_min > np.iinfo(np.int32).min and c_max < np.iinfo(np.int32).max:\n",
    "                    df[col] = df[col].astype(np.int32)\n",
    "                elif c_min > np.iinfo(np.int64).min and c_max < np.iinfo(np.int64).max:\n",
    "                    df[col] = df[col].astype(np.int64)  \n",
    "            else:\n",
    "                if use_float16 and c_min > np.finfo(np.float16).min and c_max < np.finfo(np.float16).max:\n",
    "                    df[col] = df[col].astype(np.float16)\n",
    "                elif c_min > np.finfo(np.float32).min and c_max < np.finfo(np.float32).max:\n",
    "                    df[col] = df[col].astype(np.float32)\n",
    "                else:\n",
    "                    df[col] = df[col].astype(np.float64)\n",
    "        else:\n",
    "            df[col] = df[col].astype(\"category\")\n",
    "\n",
    "    end_mem = df.memory_usage().sum() / 1024**2\n",
    "    print(\"Memory usage after optimization is: {:.2f} MB\".format(end_mem))\n",
    "    print(\"Decreased by {:.1f}%\".format(100 * (start_mem - end_mem) / start_mem))\n",
    "    \n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "def features_engineering(df):\n",
    "    \n",
    "    # Sort by localtime\n",
    "    df.sort_values(\"local_time\")\n",
    "    df.reset_index(drop=True)\n",
    "    \n",
    "    # Add more features\n",
    "    df[\"local_time\"] = pd.to_datetime(df[\"local_time\"],format=\"%Y-%m-%d %H:%M:%S\")\n",
    "    df[\"hour\"] = df[\"local_time\"].dt.hour\n",
    "    df[\"weekend\"] = df[\"local_time\"].dt.weekday\n",
    "    df['square_feet'] =  np.log1p(df['square_feet'])\n",
    "    \n",
    "    \n",
    "    # Encode Categorical Data\n",
    "    le = LabelEncoder()\n",
    "    df[\"primary_use\"] = le.fit_transform(df[\"primary_use\"])\n",
    "    \n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "def fill_weather_dataset(weather_df):\n",
    "    \n",
    "    # Find Missing Dates\n",
    "    time_format = \"%Y-%m-%d %H:%M:%S\"\n",
    "    start_date = datetime.datetime.strptime(weather_df['timestamp'].min(),time_format)\n",
    "    end_date = datetime.datetime.strptime(weather_df['timestamp'].max(),time_format)\n",
    "    total_hours = int(((end_date - start_date).total_seconds() + 3600) / 3600)\n",
    "    hours_list = [(end_date - datetime.timedelta(hours=x)).strftime(time_format) for x in range(total_hours)]\n",
    "\n",
    "    missing_hours = []\n",
    "    for site_id in range(16):\n",
    "        site_hours = np.array(weather_df[weather_df['site_id'] == site_id]['timestamp'])\n",
    "        new_rows = pd.DataFrame(np.setdiff1d(hours_list,site_hours),columns=['timestamp'])\n",
    "        new_rows['site_id'] = site_id\n",
    "        weather_df = pd.concat([weather_df,new_rows])\n",
    "\n",
    "        weather_df = weather_df.reset_index(drop=True)           \n",
    "\n",
    "    # Add new Features\n",
    "    weather_df[\"datetime\"] = pd.to_datetime(weather_df[\"timestamp\"])\n",
    "    weather_df[\"day\"] = weather_df[\"datetime\"].dt.day\n",
    "    weather_df[\"week\"] = weather_df[\"datetime\"].dt.week\n",
    "    weather_df[\"month\"] = weather_df[\"datetime\"].dt.month\n",
    "    \n",
    "    # Reset Index for Fast Update\n",
    "    weather_df = weather_df.set_index(['site_id','day','month'])\n",
    "\n",
    "    air_temperature_filler = pd.DataFrame(weather_df.groupby(['site_id','day','month'])['air_temperature'].mean(),\n",
    "                                          columns=[\"air_temperature\"])\n",
    "    weather_df.update(air_temperature_filler,overwrite=False)\n",
    "\n",
    "    # Step 1\n",
    "    cloud_coverage_filler = weather_df.groupby(['site_id','day','month'])['cloud_coverage'].mean()\n",
    "    # Step 2\n",
    "    cloud_coverage_filler = pd.DataFrame(cloud_coverage_filler.fillna(method='ffill'),columns=[\"cloud_coverage\"])\n",
    "\n",
    "    weather_df.update(cloud_coverage_filler,overwrite=False)\n",
    "\n",
    "    due_temperature_filler = pd.DataFrame(weather_df.groupby(['site_id','day','month'])['dew_temperature'].mean(),\n",
    "                                          columns=[\"dew_temperature\"])\n",
    "    weather_df.update(due_temperature_filler,overwrite=False)\n",
    "\n",
    "    # Step 1\n",
    "    sea_level_filler = weather_df.groupby(['site_id','day','month'])['sea_level_pressure'].mean()\n",
    "    # Step 2\n",
    "    sea_level_filler = pd.DataFrame(sea_level_filler.fillna(method='ffill'),columns=['sea_level_pressure'])\n",
    "\n",
    "    weather_df.update(sea_level_filler,overwrite=False)\n",
    "\n",
    "    wind_direction_filler =  pd.DataFrame(weather_df.groupby(['site_id','day','month'])['wind_direction'].mean(),\n",
    "                                          columns=['wind_direction'])\n",
    "    weather_df.update(wind_direction_filler,overwrite=False)\n",
    "\n",
    "    wind_speed_filler =  pd.DataFrame(weather_df.groupby(['site_id','day','month'])['wind_speed'].mean(),\n",
    "                                      columns=['wind_speed'])\n",
    "    weather_df.update(wind_speed_filler,overwrite=False)\n",
    "\n",
    "    # Step 1\n",
    "    precip_depth_filler = weather_df.groupby(['site_id','day','month'])['precip_depth_1_hr'].mean()\n",
    "    # Step 2\n",
    "    precip_depth_filler = pd.DataFrame(precip_depth_filler.fillna(method='ffill'),columns=['precip_depth_1_hr'])\n",
    "\n",
    "    weather_df.update(precip_depth_filler,overwrite=False)\n",
    "    \n",
    "    \n",
    "#     # Step 1\n",
    "#     hum_filler = weather_df.groupby(['site_id','day','month'])['relative_humidity(%)'].mean()\n",
    "#     # Step 2\n",
    "#     hum_filler = pd.DataFrame(hum_filler.fillna(method='ffill'),columns=['relative_humidity(%)'])\n",
    "\n",
    "#     weather_df.update(hum_filler,overwrite=False)\n",
    "    \n",
    "    \n",
    "#         # Step 1\n",
    "#     heat_filler = weather_df.groupby(['site_id','day','month'])['heat_index'].mean()\n",
    "#     # Step 2\n",
    "#     heat_filler = pd.DataFrame(heat_filler.fillna(method='ffill'),columns=['heat_index'])\n",
    "\n",
    "#     weather_df.update(heat_filler,overwrite=False)\n",
    "\n",
    "    weather_df = weather_df.reset_index()\n",
    "    weather_df = weather_df.drop(['datetime','day','week','month'],axis=1)\n",
    "        \n",
    "    return weather_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/local/home/ningzesun/.local/lib/python3.6/site-packages/ipykernel_launcher.py:15: FutureWarning: Sorting because non-concatenation axis is not aligned. A future version\n",
      "of pandas will change to not sort by default.\n",
      "\n",
      "To accept the future behavior, pass 'sort=False'.\n",
      "\n",
      "To retain the current behavior and silence the warning, pass 'sort=True'.\n",
      "\n",
      "  from ipykernel import kernelapp as app\n"
     ]
    }
   ],
   "source": [
    "# fill in the weather by group of day and month\n",
    "weather_df_fill = fill_weather_dataset(weather_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Memory usage of dataframe is 757.31 MB\n",
      "Memory usage after optimization is: 322.24 MB\n",
      "Decreased by 57.4%\n",
      "Memory usage of dataframe is 0.07 MB\n",
      "Memory usage after optimization is: 0.02 MB\n",
      "Decreased by 73.8%\n",
      "Memory usage of dataframe is 13.94 MB\n",
      "Memory usage after optimization is: 3.84 MB\n",
      "Decreased by 72.4%\n"
     ]
    }
   ],
   "source": [
    "# reduce the memory\n",
    "train_df = reduce_mem_usage(train_df,use_float16=True)\n",
    "building_df = reduce_mem_usage(building_df,use_float16=True)\n",
    "weather_df = reduce_mem_usage(weather_df_fill,use_float16=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "#select the useful columns in weather\n",
    "weather_df = weather_df.loc[:,['site_id','air_temperature','timestamp',\\\n",
    "                       'cloud_coverage','dew_temperature','precip_depth_1_hr', 'local_time']]\n",
    "# get the location by site id\n",
    "location=pd.DataFrame()\n",
    "location['site_id']=np.arange(0,16)\n",
    "location['city']=['Orlando','Heathrow','Tempe','Washington','Berkeley','Southampton',\\\n",
    "                     'Washington','Ottowa','Orlando','Austin','Saltlake','Ottowa','Dublin',\\\n",
    "                      'Minneapolis','Philadelphia','Rochester']\n",
    "location['country']=['US','UK','US','US','US','UK',\\\n",
    "                    'US','Montreal','US','US','US','Montreal','Ireland',\\\n",
    "                    'US','US','US']\n",
    "weather_df= weather_df.merge(location, on='site_id', how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [],
   "source": [
    "# setup the function of find location time\n",
    "from datetime import datetime\n",
    "from datetime import timedelta\n",
    "def getlocaltime(row):\n",
    "        if row['timestamp']< datetime.strptime('2016-03-13 02:00:00', '%Y-%m-%d %H:%M:%S') and row['country']=='Montreal':\n",
    "            val=row['timestamp']+timedelta(hours=-5)\n",
    "        elif  row['timestamp']>=datetime.strptime('2016-03-13 02:00:00', '%Y-%m-%d %H:%M:%S') and \\\n",
    "        row['timestamp']< datetime.strptime('2016-11-06 02:00:00', '%Y-%m-%d %H:%M:%S') and row['country']=='Montreal':\n",
    "            val=row['timestamp']+timedelta(hours=-4)\n",
    "        elif row['timestamp']>=datetime.strptime('2016-11-06 02:00:00', '%Y-%m-%d %H:%M:%S') and\\\n",
    "        row['timestamp']< datetime.strptime('2017-03-12 02:00:00', '%Y-%m-%d %H:%M:%S') and row['country']=='Montreal':\n",
    "            val=row['timestamp']+timedelta(hours=-5)\n",
    "        elif row['timestamp']>= datetime.strptime('2017-03-12 02:00:00', '%Y-%m-%d %H:%M:%S') and \\\n",
    "        row['timestamp']< datetime.strptime('2017-11-05 02:00:00', '%Y-%m-%d %H:%M:%S') and row['country']=='Montreal':\n",
    "            val=row['timestamp']+timedelta(hours=-4)\n",
    "        elif row['timestamp']>=datetime.strptime('2017-11-05 02:00:00', '%Y-%m-%d %H:%M:%S') and \\\n",
    "        row['timestamp']< datetime.strptime('2018-03-11 02:00:00', '%Y-%m-%d %H:%M:%S') and row['country']=='Montreal':\n",
    "            val=row['timestamp']+timedelta(hours=-5)\n",
    "        elif row['timestamp']>=datetime.strptime('2018-03-11 02:00:00', '%Y-%m-%d %H:%M:%S') and \\\n",
    "        row['timestamp']<datetime.strptime('2018-11-04 02:00:00', '%Y-%m-%d %H:%M:%S') and row['country']=='Montreal':\n",
    "            val=row['timestamp']+timedelta(hours=-4)\n",
    "        elif row['timestamp']>=datetime.strptime('2018-11-04 02:00:00', '%Y-%m-%d %H:%M:%S') and row['country']=='Montreal':\n",
    "            val=row['timestamp']+timedelta(hours=-5)\n",
    "\n",
    "        elif row['timestamp']< datetime.strptime('2016-03-27 01:00:00', '%Y-%m-%d %H:%M:%S') and row['country'] in ['UK','Ireland']:\n",
    "            val=row['timestamp']\n",
    "        elif  row['timestamp']>=datetime.strptime('2016-03-27 01:00:00', '%Y-%m-%d %H:%M:%S') and \\\n",
    "        row['timestamp']< datetime.strptime('2016-10-30 02:00:00', '%Y-%m-%d %H:%M:%S') and row['country'] in ['UK','Ireland'] :\n",
    "            val=row['timestamp']+timedelta(hours=1)\n",
    "        elif row['timestamp']>=datetime.strptime('2016-10-30 02:00:00', '%Y-%m-%d %H:%M:%S') and\\\n",
    "        row['timestamp']< datetime.strptime('2017-03-26 01:00:00', '%Y-%m-%d %H:%M:%S') and row['country'] in ['UK','Ireland']:\n",
    "            val=row['timestamp']\n",
    "        elif row['timestamp']>= datetime.strptime('2017-03-26 01:00:00', '%Y-%m-%d %H:%M:%S') and \\\n",
    "        row['timestamp']< datetime.strptime('2017-10-29 02:00:00', '%Y-%m-%d %H:%M:%S') and row['country'] in ['UK','Ireland']:\n",
    "            val=row['timestamp']+timedelta(hours=1)\n",
    "        elif row['timestamp']>=datetime.strptime('2017-10-29 02:00:00', '%Y-%m-%d %H:%M:%S') and \\\n",
    "        row['timestamp']< datetime.strptime('2018-03-25 01:00:00', '%Y-%m-%d %H:%M:%S') and row['country'] in ['UK','Ireland']:\n",
    "            val=row['timestamp']\n",
    "        elif row['timestamp']>=datetime.strptime('2018-03-25 01:00:00', '%Y-%m-%d %H:%M:%S') and \\\n",
    "        row['timestamp']<datetime.strptime('2018-10-28 02:00:00', '%Y-%m-%d %H:%M:%S') and row['country'] in ['UK','Ireland']:\n",
    "            val=row['timestamp']+timedelta(hours=1)\n",
    "        elif row['timestamp']>=datetime.strptime('2018-10-28 02:00:00', '%Y-%m-%d %H:%M:%S') and row['country'] in ['UK','Ireland']:\n",
    "            val=row['timestamp']\n",
    "\n",
    "        elif row['city']=='Tempe':\n",
    "            val=row['timestamp']+timedelta(hours=-7)\n",
    "\n",
    "        elif row['timestamp']< datetime.strptime('2016-03-13 02:00:00', '%Y-%m-%d %H:%M:%S') and row['city'] in ['Orlando',\\\n",
    "        'Washington','Philadelphia','Rochester']:\n",
    "            val=row['timestamp']+timedelta(hours=-5)\n",
    "        elif  row['timestamp']>=datetime.strptime('2016-03-13 02:00:00', '%Y-%m-%d %H:%M:%S') and \\\n",
    "        row['timestamp']< datetime.strptime('2016-11-06 02:00:00', '%Y-%m-%d %H:%M:%S') and row['city'] in ['Orlando',\\\n",
    "        'Washington','Philadelphia','Rochester']:\n",
    "            val=row['timestamp']+timedelta(hours=-4)\n",
    "        elif row['timestamp']>=datetime.strptime('2016-11-06 02:00:00', '%Y-%m-%d %H:%M:%S') and\\\n",
    "        row['timestamp']< datetime.strptime('2017-03-12 02:00:00', '%Y-%m-%d %H:%M:%S') and row['city'] in ['Orlando',\\\n",
    "        'Washington','Philadelphia','Rochester']:\n",
    "            val=row['timestamp']+timedelta(hours=-5)\n",
    "        elif row['timestamp']>= datetime.strptime('2017-03-12 02:00:00', '%Y-%m-%d %H:%M:%S') and \\\n",
    "        row['timestamp']< datetime.strptime('2017-11-05 02:00:00', '%Y-%m-%d %H:%M:%S') and row['city'] in ['Orlando',\\\n",
    "        'Washington','Philadelphia','Rochester']:\n",
    "            val=row['timestamp']+timedelta(hours=-4)\n",
    "        elif row['timestamp']>=datetime.strptime('2017-11-05 02:00:00', '%Y-%m-%d %H:%M:%S') and \\\n",
    "        row['timestamp']< datetime.strptime('2018-03-11 02:00:00', '%Y-%m-%d %H:%M:%S') and row['city'] in ['Orlando',\\\n",
    "        'Washington','Philadelphia','Rochester']:\n",
    "            val=row['timestamp']+timedelta(hours=-5)\n",
    "        elif row['timestamp']>=datetime.strptime('2018-03-11 02:00:00', '%Y-%m-%d %H:%M:%S') and \\\n",
    "        row['timestamp']<datetime.strptime('2018-11-04 02:00:00', '%Y-%m-%d %H:%M:%S') and row['city'] in ['Orlando',\\\n",
    "        'Washington','Philadelphia','Rochester']:\n",
    "            val=row['timestamp']+timedelta(hours=-4)\n",
    "        elif row['timestamp']>=datetime.strptime('2018-11-04 02:00:00', '%Y-%m-%d %H:%M:%S') and row['city'] in ['Orlando',\\\n",
    "        'Washington','Philadelphia','Rochester']:\n",
    "            val=row['timestamp']+timedelta(hours=-5)\n",
    "\n",
    "        elif row['timestamp']< datetime.strptime('2016-03-13 02:00:00', '%Y-%m-%d %H:%M:%S') and row['city'] in['Austin',\\\n",
    "        'Minneapolis']:\n",
    "            val=row['timestamp']+timedelta(hours=-6)\n",
    "        elif  row['timestamp']>=datetime.strptime('2016-03-13 02:00:00', '%Y-%m-%d %H:%M:%S') and \\\n",
    "        row['timestamp']< datetime.strptime('2016-11-06 02:00:00', '%Y-%m-%d %H:%M:%S') and row['city'] in['Austin',\\\n",
    "        'Minneapolis']:\n",
    "            val=row['timestamp']+timedelta(hours=-5)\n",
    "        elif row['timestamp']>=datetime.strptime('2016-11-06 02:00:00', '%Y-%m-%d %H:%M:%S') and\\\n",
    "        row['timestamp']< datetime.strptime('2017-03-12 02:00:00', '%Y-%m-%d %H:%M:%S') and  row['city'] in['Austin',\\\n",
    "        'Minneapolis']:\n",
    "            val=row['timestamp']+timedelta(hours=-6)\n",
    "        elif row['timestamp']>= datetime.strptime('2017-03-12 02:00:00', '%Y-%m-%d %H:%M:%S') and \\\n",
    "        row['timestamp']< datetime.strptime('2017-11-05 02:00:00', '%Y-%m-%d %H:%M:%S') and  row['city'] in['Austin',\\\n",
    "        'Minneapolis']:\n",
    "            val=row['timestamp']+timedelta(hours=-5)\n",
    "        elif row['timestamp']>=datetime.strptime('2017-11-05 02:00:00', '%Y-%m-%d %H:%M:%S') and \\\n",
    "        row['timestamp']< datetime.strptime('2018-03-11 02:00:00', '%Y-%m-%d %H:%M:%S') and  row['city'] in['Austin',\\\n",
    "        'Minneapolis']:\n",
    "            val=row['timestamp']+timedelta(hours=-6)\n",
    "        elif row['timestamp']>=datetime.strptime('2018-03-11 02:00:00', '%Y-%m-%d %H:%M:%S') and \\\n",
    "        row['timestamp']<datetime.strptime('2018-11-04 02:00:00', '%Y-%m-%d %H:%M:%S') and  row['city'] in['Austin',\\\n",
    "        'Minneapolis']:\n",
    "            val=row['timestamp']+timedelta(hours=-5)\n",
    "        elif row['timestamp']>=datetime.strptime('2018-11-04 02:00:00', '%Y-%m-%d %H:%M:%S') and  row['city'] in['Austin',\\\n",
    "        'Minneapolis']:\n",
    "            val=row['timestamp']+timedelta(hours=-6)\n",
    "\n",
    "\n",
    "        elif row['timestamp']< datetime.strptime('2016-03-13 02:00:00', '%Y-%m-%d %H:%M:%S') and row['city'] in ['Saltlake']:\n",
    "            val=row['timestamp']+timedelta(hours=-7)\n",
    "        elif  row['timestamp']>=datetime.strptime('2016-03-13 02:00:00', '%Y-%m-%d %H:%M:%S') and \\\n",
    "        row['timestamp']< datetime.strptime('2016-11-06 02:00:00', '%Y-%m-%d %H:%M:%S') and  row['city'] in ['Saltlake']:\n",
    "            val=row['timestamp']+timedelta(hours=-6)\n",
    "        elif row['timestamp']>=datetime.strptime('2016-11-06 02:00:00', '%Y-%m-%d %H:%M:%S') and\\\n",
    "        row['timestamp']< datetime.strptime('2017-03-12 02:00:00', '%Y-%m-%d %H:%M:%S') and   row['city'] in ['Saltlake']:\n",
    "            val=row['timestamp']+timedelta(hours=-7)\n",
    "        elif row['timestamp']>= datetime.strptime('2017-03-12 02:00:00', '%Y-%m-%d %H:%M:%S') and \\\n",
    "        row['timestamp']< datetime.strptime('2017-11-05 02:00:00', '%Y-%m-%d %H:%M:%S') and   row['city'] in ['Saltlake']:\n",
    "            val=row['timestamp']+timedelta(hours=-6)\n",
    "        elif row['timestamp']>=datetime.strptime('2017-11-05 02:00:00', '%Y-%m-%d %H:%M:%S') and \\\n",
    "        row['timestamp']< datetime.strptime('2018-03-11 02:00:00', '%Y-%m-%d %H:%M:%S') and   row['city'] in ['Saltlake']:\n",
    "            val=row['timestamp']+timedelta(hours=-7)\n",
    "        elif row['timestamp']>=datetime.strptime('2018-03-11 02:00:00', '%Y-%m-%d %H:%M:%S') and \\\n",
    "        row['timestamp']<datetime.strptime('2018-11-04 02:00:00', '%Y-%m-%d %H:%M:%S') and   row['city'] in ['Saltlake']:\n",
    "            val=row['timestamp']+timedelta(hours=-6)\n",
    "        elif row['timestamp']>=datetime.strptime('2018-11-04 02:00:00', '%Y-%m-%d %H:%M:%S') and  row['city'] in ['Saltlake']:\n",
    "            val=row['timestamp']+timedelta(hours=-7)\n",
    "\n",
    "        elif row['timestamp']< datetime.strptime('2016-03-13 02:00:00', '%Y-%m-%d %H:%M:%S') and row['city'] in ['Berkeley']:\n",
    "            val=row['timestamp']+timedelta(hours=-8)\n",
    "        elif  row['timestamp']>=datetime.strptime('2016-03-13 02:00:00', '%Y-%m-%d %H:%M:%S') and \\\n",
    "        row['timestamp']< datetime.strptime('2016-11-06 02:00:00', '%Y-%m-%d %H:%M:%S') and  row['city'] in ['Berkeley']:\n",
    "            val=row['timestamp']+timedelta(hours=-7)\n",
    "        elif row['timestamp']>=datetime.strptime('2016-11-06 02:00:00', '%Y-%m-%d %H:%M:%S') and\\\n",
    "        row['timestamp']< datetime.strptime('2017-03-12 02:00:00', '%Y-%m-%d %H:%M:%S') and  row['city'] in ['Berkeley']:\n",
    "            val=row['timestamp']+timedelta(hours=-8)\n",
    "        elif row['timestamp']>= datetime.strptime('2017-03-12 02:00:00', '%Y-%m-%d %H:%M:%S') and \\\n",
    "        row['timestamp']< datetime.strptime('2017-11-05 02:00:00', '%Y-%m-%d %H:%M:%S') and  row['city'] in ['Berkeley']:\n",
    "            val=row['timestamp']+timedelta(hours=-7)\n",
    "        elif row['timestamp']>=datetime.strptime('2017-11-05 02:00:00', '%Y-%m-%d %H:%M:%S') and \\\n",
    "        row['timestamp']< datetime.strptime('2018-03-11 02:00:00', '%Y-%m-%d %H:%M:%S') and  row['city'] in ['Berkeley']:\n",
    "            val=row['timestamp']+timedelta(hours=-8)\n",
    "        elif row['timestamp']>=datetime.strptime('2018-03-11 02:00:00', '%Y-%m-%d %H:%M:%S') and \\\n",
    "        row['timestamp']<datetime.strptime('2018-11-04 02:00:00', '%Y-%m-%d %H:%M:%S') and  row['city'] in ['Berkeley']:\n",
    "            val=row['timestamp']+timedelta(hours=-7)\n",
    "        elif row['timestamp']>=datetime.strptime('2018-11-04 02:00:00', '%Y-%m-%d %H:%M:%S') and row['city'] in ['Berkeley']:\n",
    "            val=row['timestamp']+timedelta(hours=-8)    \n",
    "        return val"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "# get the location time\n",
    "weather_df[\"timestamp\"] = pd.to_datetime(weather_df[\"timestamp\"],format=\"%Y-%m-%d %H:%M:%S\")\n",
    "weather_df['local_time']=weather_df.apply(getlocaltime, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "# get holiday information\n",
    "from datetime import date\n",
    "import holidays\n",
    "UK=[]\n",
    "for ptr in holidays.UnitedKingdom(years=2016).keys():\n",
    "    UK.append(str(ptr))\n",
    "for ptr in holidays.UnitedKingdom(years=2017).keys():\n",
    "    UK.append(str(ptr))\n",
    "for ptr in holidays.UnitedKingdom(years=2018).keys():\n",
    "    UK.append(str(ptr))\n",
    "    UK.append('2019-01-01')\n",
    "IR=[]\n",
    "for ptr in holidays.Ireland(years=2016).keys():\n",
    "    IR.append(str(ptr))\n",
    "for ptr in holidays.Ireland(years=2017).keys():\n",
    "    IR.append(str(ptr))\n",
    "for ptr in holidays.Ireland(years=2018).keys():\n",
    "    IR.append(str(ptr))\n",
    "    IR.append('2019-01-01')\n",
    "US=[]\n",
    "for ptr in holidays.UnitedStates(years=2016).keys():\n",
    "    US.append(str(ptr))\n",
    "for ptr in holidays.UnitedStates(years=2017).keys():\n",
    "    US.append(str(ptr))\n",
    "for ptr in holidays.UnitedStates(years=2018).keys():\n",
    "    US.append(str(ptr))\n",
    "    US.append('2019-01-01')\n",
    "CA=[]\n",
    "for ptr in holidays.Canada(years=2016).keys():\n",
    "    CA.append(str(ptr))\n",
    "for ptr in holidays.Canada(years=2017).keys():\n",
    "    CA.append(str(ptr))\n",
    "for ptr in holidays.Canada(years=2018).keys():\n",
    "    CA.append(str(ptr))\n",
    "    CA.append('2019-01-01')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "# setup the function to test whether it is the holiday\n",
    "def is_holiday(df):\n",
    "    df['is_holiday']=[0]*(df.shape[0])\n",
    "    df.loc[df['country']=='US','is_holiday']=(df['local_time'].dt.date.astype('str').isin(US)).astype(int)\n",
    "    df.loc[df['country']=='UK','is_holiday']=(df['local_time'].dt.date.astype('str').isin(UK)).astype(int)\n",
    "    df.loc[df['country']=='Montreal','is_holiday']=(df['local_time'].dt.date.astype('str').isin(CA)).astype(int)\n",
    "    df.loc[df['country']=='Ireland','is_holiday']=(df['local_time'].dt.date.astype('str').isin(IR)).astype(int)\n",
    "    return df\n",
    "\n",
    "# get the holiday\n",
    "weather_df=is_holiday(weather_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [],
   "source": [
    "path = \"../../Resources/\"\n",
    "train_df = pd.read_csv(path + 'train.csv')\n",
    "# Remove outliers\n",
    "train_df = train_df.loc[train_df['building_id'] != 1099 ]\n",
    "train_df = train_df.query('not (building_id <= 104 & meter == 0 & timestamp <= \"2016-05-20\")')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Memory usage of dataframe is 757.31 MB\n",
      "Memory usage after optimization is: 322.24 MB\n",
      "Decreased by 57.4%\n"
     ]
    }
   ],
   "source": [
    "train_df = reduce_mem_usage(train_df,use_float16=True)\n",
    "# weather_df.drop(['city', 'country'], axis = 1, inplace = True)\n",
    "train_df[\"timestamp\"] = pd.to_datetime(train_df[\"timestamp\"],format=\"%Y-%m-%d %H:%M:%S\")\n",
    "weather_df[\"timestamp\"] = pd.to_datetime(weather_df[\"timestamp\"],format=\"%Y-%m-%d %H:%M:%S\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [],
   "source": [
    "# output the initial merged train csv\n",
    "weather_df = weather_df.loc[:,['site_id','air_temperature','timestamp',\\\n",
    "                       'cloud_coverage','dew_temperature','precip_depth_1_hr', 'local_time']]\n",
    "train_df = pd.merge(train_df, building_df, on='building_id',how='left')\n",
    "train_df = pd.merge(train_df, weather_df,how='left', on=['site_id','timestamp'])\n",
    "train_df.to_csv('../../Large_output/train_merge.csv', index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [],
   "source": [
    "# output the engineered merged train csv\n",
    "train_engineer = features_engineering(train_df)\n",
    "train_engineer = train_engineer.loc[:,['building_id', 'meter','site_id','primary_use', 'square_feet',\n",
    "                                       'air_temperature', 'cloud_coverage','dew_temperature',\n",
    "                                       'precip_depth_1_hr','hour', 'weekend','is_holiday', 'meter_reading']]\n",
    "train_engineer.to_csv('../../Large_output/train_engineer.csv', index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/local/home/ningzesun/.local/lib/python3.6/site-packages/ipykernel_launcher.py:15: FutureWarning: Sorting because non-concatenation axis is not aligned. A future version\n",
      "of pandas will change to not sort by default.\n",
      "\n",
      "To accept the future behavior, pass 'sort=False'.\n",
      "\n",
      "To retain the current behavior and silence the warning, pass 'sort=True'.\n",
      "\n",
      "  from ipykernel import kernelapp as app\n"
     ]
    }
   ],
   "source": [
    "# get the test wheather information and clean\n",
    "import datetime\n",
    "weather_df = pd.read_csv(path + 'weather_test_df.csv')\n",
    "weather_df_fill = fill_weather_dataset(weather_df)\n",
    "from datetime import datetime\n",
    "from datetime import timedelta\n",
    "weather_df_fill[\"timestamp\"] = pd.to_datetime(weather_df_fill[\"timestamp\"],format=\"%Y-%m-%d %H:%M:%S\")\n",
    "weather_df_fill= weather_df_fill.merge(location, on='site_id', how='left')\n",
    "weather_df_fill.drop(['city_x', 'country_x'], axis = 1, inplace = True)\n",
    "weather_df_fill = weather_df_fill.rename({'city_y': 'city', 'country_y': 'country'})\n",
    "weather_df_fill['local_time']=weather_df_fill.apply(getlocaltime, axis=1)\n",
    "from datetime import date\n",
    "weather_df_fill=is_holiday(weather_df_fill)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {},
   "outputs": [],
   "source": [
    "# output the test merged and engineered csv\n",
    "path = \"../../Resources/\"\n",
    "test_df = pd.read_csv(path + 'test.csv')\n",
    "test_df[\"timestamp\"] = pd.to_datetime(test_df[\"timestamp\"],format=\"%Y-%m-%d %H:%M:%S\")\n",
    "weather_df_fill[\"timestamp\"] = pd.to_datetime(weather_df_fill[\"timestamp\"],format=\"%Y-%m-%d %H:%M:%S\")\n",
    "test_df = pd.merge(test_df, building_df, on='building_id',how='left')\n",
    "test_df = pd.merge(test_df, weather_df,how='left', on=['site_id','timestamp'])\n",
    "test_df.to_csv('../../Large_output/test_merge.csv', index = False)\n",
    "test_engineer = features_engineering(test_df)\n",
    "test_engineer = test_engineer.loc[:,['building_id', 'meter','site_id','primary_use', 'square_feet','air_temperature',\\\n",
    "                    'cloud_coverage','dew_temperature','precip_depth_1_hr','hour', 'weekend','is_holiday', 'row_id']]\n",
    "test_engineer.to_csv('../../Large_output/test_engineer.csv', index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
